# PowerShell script to initialize WVP database
Write-Host "Initializing WVP database..." -ForegroundColor Green

# Check if MySQL is running
$mysqlRunning = Get-NetTCPConnection -LocalPort 3306 -ErrorAction SilentlyContinue
if (-not $mysqlRunning) {
    Write-Host "Error: MySQL service is not running. Please start MySQL service first." -ForegroundColor Red
    Read-Host "Press Enter to exit"
    exit 1
}

Write-Host "MySQL service is running..." -ForegroundColor Green

# Try to find MySQL client
$mysqlPaths = @(
    "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe",
    "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe", 
    "C:\xampp\mysql\bin\mysql.exe",
    "C:\wamp64\bin\mysql\mysql8.0.21\bin\mysql.exe"
)

$mysqlClient = $null
foreach ($path in $mysqlPaths) {
    if (Test-Path $path) {
        $mysqlClient = $path
        break
    }
}

# Try to find mysql in PATH
if (-not $mysqlClient) {
    try {
        $mysqlClient = (Get-Command mysql -ErrorAction Stop).Source
    } catch {
        # mysql not found in PATH
    }
}

if (-not $mysqlClient) {
    Write-Host "MySQL client not found. Please run the following command manually:" -ForegroundColor Yellow
    Write-Host "mysql -h 127.0.0.1 -P 3306 -u root -p12345678 < mysql_init_script_complete.sql" -ForegroundColor Cyan
    Read-Host "Press Enter to exit"
    exit 1
}

Write-Host "Found MySQL client: $mysqlClient" -ForegroundColor Green

# Check if SQL file exists
if (-not (Test-Path "mysql_init_script_complete.sql")) {
    Write-Host "Error: Database initialization script not found: mysql_init_script_complete.sql" -ForegroundColor Red
    Read-Host "Press Enter to exit"
    exit 1
}

Write-Host "Executing database initialization script..." -ForegroundColor Yellow

# Execute the SQL script
try {
    $process = Start-Process -FilePath $mysqlClient -ArgumentList "-h", "127.0.0.1", "-P", "3306", "-u", "root", "-p12345678" -RedirectStandardInput "mysql_init_script_complete.sql" -Wait -PassThru -NoNewWindow
    
    if ($process.ExitCode -eq 0) {
        Write-Host ""
        Write-Host "✓ Database initialization successful!" -ForegroundColor Green
        Write-Host "You can now start the WVP application." -ForegroundColor Green
    } else {
        Write-Host ""
        Write-Host "✗ Database initialization failed. Exit code: $($process.ExitCode)" -ForegroundColor Red
        Write-Host "Please check:" -ForegroundColor Yellow
        Write-Host "1. MySQL service is running properly" -ForegroundColor Yellow
        Write-Host "2. Username and password are correct (root/12345678)" -ForegroundColor Yellow
        Write-Host "3. Database connection is working" -ForegroundColor Yellow
    }
} catch {
    Write-Host "Error executing MySQL command: $($_.Exception.Message)" -ForegroundColor Red
}

Write-Host ""
Read-Host "Press Enter to exit"
